﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;

namespace WBrowser.MY_CODE
{
    public partial class TestSQLCe : Form
    {
        #region TestSQLCe
        public TestSQLCe()
        {
            InitializeComponent();
        }
        #endregion

        #region btnCreateDatabase_Click

        private void btnCreateDatabase_Click(object sender, EventArgs e)
        {
            string connectionString;
            string fileName = "ArcaneCode.sdf";
            string password = "arcanecode";
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }

            connectionString = string.Format(
            "DataSource=\"{0}\"; Password='{1}'", fileName, password);
            SqlCeEngine en = new SqlCeEngine(connectionString);
            en.CreateDatabase();
            lblResults.Text = "Database Created.";
        }
        #endregion

        #region btnCreateTable_Click

        private void btnCreateTable_Click(object sender, EventArgs e)
        {
            SqlCeConnection cn = new SqlCeConnection(GeneralClass.ConnectString());
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }

            SqlCeCommand cmd;

            string sql = "create table CoolPeople ("
            + "LastName nvarchar (40) not null, "
            + "FirstName nvarchar (40), "
            + "URL nvarchar (256) )";

            cmd = new SqlCeCommand(sql, cn);
            try
            {
                cmd.ExecuteNonQuery();
                lblResults.Text = "Table Created.";
            }

            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                cn.Close();
            }
        }
        #endregion

        #region btnLoadTable_Click

        private void btnLoadTable_Click(object sender, EventArgs e)
        {
            try
            {
                LoadARow("Carl", "Franklin", @"http:\\www.dnrtv.com");
                LoadARow("Richard", "Campbell", @"http:\\www.dotnetrocks.com");
                LoadARow("Leo", "Laporte", @"http:\\www.twit.tv");
                LoadARow("Steve", "Gibson", @"http:\\www.grc.com");
                LoadARow("Arcane", "Code", @"http:\\arcanecode.wordpress.com");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        #endregion

        #region LoadARow

        private void LoadARow(string first, string last, string url)
        {
            SqlCeConnection cn = new SqlCeConnection(GeneralClass.ConnectString());

            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            
            SqlCeCommand cmd;
            string sql = "insert into CoolPeople "
            + "(LastName, FirstName, URL) "
            + "values (@lastname, @firstname, @url)";

            try
            {
                cmd = new SqlCeCommand(sql, cn);
                cmd.Parameters.AddWithValue("@lastname", last);
                cmd.Parameters.AddWithValue("@firstname", first);
                cmd.Parameters.AddWithValue("@url", url);
                cmd.ExecuteNonQuery();
                lblResults.Text = "Row Added.";
            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                cn.Close();
            }
        }
        #endregion

        #region cmdLoadDataGrid_Click
        private void cmdLoadDataGrid_Click(object sender, EventArgs e)
        {
            SqlCeConnection cn = new SqlCeConnection(GeneralClass.ConnectString());
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            try
            {
                // Set the command to use the table, not a query
                SqlCeCommand cmd = new SqlCeCommand("CoolPeople", cn);
                cmd.CommandType = CommandType.TableDirect;

                // Get the table
                SqlCeResultSet rs = cmd.ExecuteResultSet(
                ResultSetOptions.Scrollable);

                // load the result set into the datasource
                dgvCoolPeople.DataSource = rs;
            }

            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message, "Oh Crap.",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Crap.",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            // Note, do not close the connection,
            // if you do the grid won't be able to display.
            // For production code you probably want to make
            // your result set (rs) a class level variable
        }

        #endregion



        #region btnReadRecords_Click

        private void btnReadRecords_Click(object sender, EventArgs e)
        {
            SqlCeConnection cn = new SqlCeConnection(GeneralClass.ConnectString());
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }

            // Build the sql query. If this was real life,
            // I'd use a parameter for the where bit
            // to avoid SQL Injection attacks.
            string sql = "select LastName, FirstName from CoolPeople ";
            if (txtName.Text.Length > 0)
            {
                sql += "where LastName like '" + txtName.Text + "%' ";
            }

            try
            {
                SqlCeCommand cmd = new SqlCeCommand(sql, cn);
                cmd.CommandType = CommandType.Text;

                // if you don't set the result set to
                // scrollable HasRows does not work
                SqlCeResultSet rs = cmd.ExecuteResultSet(
                ResultSetOptions.Scrollable);

                // If you need to be able to update the result set, instead use:
                // SqlCeResultSet rs = cmd.ExecuteResultSet(
                // ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
                if (rs.HasRows)
                {
                    // Use the get ordinal function so you don't
                    // have to worry about remembering what
                    // order your SQL put the field names in.
                    int ordLastName = rs.GetOrdinal("LastName");
                    int ordFirstname = rs.GetOrdinal("FirstName");

                    // Hold the output
                    StringBuilder output = new StringBuilder();

                    // Read the first record and get it's data
                    rs.ReadFirst();
                    output.AppendLine(rs.GetString(ordFirstname)
                    + " " + rs.GetString(ordLastName));

                    // Now read thru the rest of the records.
                    // When there's no more data, .Read returns false.
                    while (rs.Read())
                    {
                        output.AppendLine(rs.GetString(ordFirstname)
                        + " " + rs.GetString(ordLastName));
                    }

                    // Set the output in the label
                    lblResults.Text = output.ToString();
                }
                else
                {
                    lblResults.Text = "No Rows Found.";
                }
            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message, "Oh Crap.",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Crap.",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // Don't need it anymore so we'll be good and close it.
                // in a 'real life' situation
                // cn would likely be class level
                cn.Close();
            }
        }
        #endregion

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void btnGoForm2_Click(object sender, EventArgs e)
        {
        }
    }
}